package com.adcc.monitoragent.core.service;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.quartz.DisallowConcurrentExecution;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.PersistJobDataAfterExecution;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.adcc.monitoragent.bean.DatabaseBean;
import com.adcc.monitoragent.log.ServiceLogger;
import com.adcc.monitoragent.send.MessageSend;
import com.adcc.monitoragent.util.Constants;
import com.adcc.monitoragent.util.ProtertiesUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;


/**
 * url + username 唯一确定数据库
 * 数据库监控类，支持mysql，oracle等
 * 
 * @author charlie
 * 
 */
@PersistJobDataAfterExecution   
@DisallowConcurrentExecution
public class DatabaseService implements Job {
	private final static Logger log = LoggerFactory.getLogger(DatabaseService.class);
	/**
	 * 数据库连接池
	 */
	private static Map<String, ComboPooledDataSource> connPool = new ConcurrentHashMap<String, ComboPooledDataSource>();
	private static Map<String, DatabaseBean> lastDatabaseBeanMap = new ConcurrentHashMap<String, DatabaseBean>();
	/**
	 * 获取数据库连接
	 * @param driver
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 * @throws Exception
	 */
	private static Connection getConn(String driver, String url,
			String username, String password) throws Exception {
		ComboPooledDataSource cpds = connPool.get(url);
		if(cpds != null) {
			return cpds.getConnection();
		}
		cpds = new ComboPooledDataSource();
        try {
            cpds.setDriverClass( driver );
            cpds.setJdbcUrl(url);
            cpds.setUser(username);                                  
            cpds.setPassword(password);                                  
                
            // the settings below are optional -- c3p0 can work with defaults
            cpds.setMinPoolSize(1);                                     
            cpds.setAcquireIncrement(1);
            cpds.setAcquireRetryAttempts(1);
            cpds.setMaxPoolSize(1);
            cpds.setAcquireRetryDelay(1);
            cpds.setCheckoutTimeout(1000);
            connPool.put(url, cpds);
        } catch (PropertyVetoException e1) {
        	log.error("数据库连接池初始化失败:" + e1.getMessage());
            e1.printStackTrace();
            return null;
        } //loads the jdbc driver            
		return cpds.getConnection();
	}
	/**
	 * 获取数据库监控信息
	 * @param driver
	 * @param url
	 * @param username
	 * @param password
	 * @return
	 */
	public static DatabaseBean getInfo(String driver, String url,
			String username, String password) {
		DatabaseBean dbBean = new DatabaseBean();
		dbBean.setDriverName(driver);
		dbBean.setUrl(url);
		dbBean.setUsername(username);
		//计算ip
		try {
			dbBean.setIp(url.split("@")[1].split(":")[0]);
		} catch (Exception e1) {
		}
		Connection conn = null;
		Statement st = null;
		try {
			conn = getConn(driver, url, username, password);
			DatabaseMetaData meta = conn.getMetaData();
			dbBean.setCatalog(conn.getCatalog());
			dbBean.setName(meta.getDatabaseProductName());
			dbBean.setVersion(meta.getDatabaseProductVersion());
			dbBean.setDriverVersion(meta.getDriverVersion());
			dbBean.setKeywords(meta.getSQLKeywords());
			try {
				st = conn.createStatement();
				if(dbBean.getName().contains("Oracle")) {
					st.execute("select * from user_tab_comments");
				} else {
					st.execute("select 1");
				}
				dbBean.setStatus(Constants.STATUS_OK);
			} catch (Exception e) {
				log.error("获取数据库状态失败" + e.getMessage());
				dbBean.setStatus(Constants.STATUS_ERROR);
			}
		} catch (Exception e) {
			log.error("获取数据库信息失败" + e.getMessage());
			dbBean.setStatus(Constants.STATUS_ERROR);
		} finally {
			if(st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					log.error("关闭数据库Statement失败" + e.getMessage());
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					log.error("关闭数据库连接失败" + e.getMessage());
				}
			}
		}
		
		return dbBean;
	}
	
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		log.debug("定时执行任务。。。。。。。。。。。。");
		for (Object objStr : ProtertiesUtil.getDbConnectString()) {
			try {
				String[] s = objStr.toString().split("-");
				DatabaseBean db = DatabaseService.getInfo(s[0], s[1], 
						ProtertiesUtil.getDbUsername(),	ProtertiesUtil.getDbPassword());
				String key = db.buildKey();
				DatabaseBean lastDatabaseBean = lastDatabaseBeanMap.get(key);
				boolean statusChange = lastDatabaseBean != null && !lastDatabaseBean.getStatus().equals(db.getStatus());
				if(db.getStatus().equals(Constants.STATUS_ERROR) && statusChange) {//如果状态异常 并且 状态改变
					log.warn("数据库状态正常转异常，休眠2秒重试，防止误判");
					try {
						Thread.sleep(2000);
					} catch (InterruptedException e) {
					}
					db = DatabaseService.getInfo(s[0], s[1], 
							ProtertiesUtil.getDbUsername(),	ProtertiesUtil.getDbPassword());
					if(db.getStatus().equals(Constants.STATUS_ERROR)) {//状态还是异常
						String desc = "服务停止:(" + db.getUrl() + ":" + db.getUsername() + ")" + 
								lastDatabaseBean.getStatus() + "->" + db.getStatus();
						ServiceLogger.error(db.getIp(), null, "db", desc);
					}
				} else if(statusChange){//状态由异常转正常
					String desc = "服务启动:(" + db.getUrl() + ":" + db.getUsername() + ")" + 
							lastDatabaseBean.getStatus() + "->" + db.getStatus();
					ServiceLogger.info(db.getIp(), null, "db", desc);
				}
				
				lastDatabaseBeanMap.put(key, db);
				//转发消息
				MessageSend.sendMsg(db);
			} catch (Exception e) {
				log.error("数据库定时任务执行失败:" + objStr +  e.getMessage());
			}
		}
		log.debug("完成任务。。。。。。。。。。。。");
	}
	
	public static void main(String[] args) throws Exception {
		DatabaseBean db = DatabaseService.getInfo("oracle.jdbc.OracleDriver",
				"jdbc:oracle:thin:orcl/skycenter@192.168.243.73:1521:orcl", "skycenter",
				"skycenterdb");
		System.out.println(db.toString());
	}
}
